package me.zhengjie.modules.docparse.parse;

import me.zhengjie.modules.docparse.domain.dto.FileDto;
import me.zhengjie.modules.docparse.domain.vo.StructableExcelVo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.NumberFormat;
import java.util.*;

@me.zhengjie.modules.docparse.annotation.Parser(fileType = {"xls", "xlsx"})
public class ExcelParser implements Parser<StructableExcelVo> {

    @Override
    public StructableExcelVo parse(String path) throws IOException {
        if (path.toUpperCase().endsWith(".XLSX")) {
            return xlsx2String(path);
        }
        if (path.toUpperCase().endsWith(".XLS")) {
            return xls2String(path);
        }
        return null;
    }

    @Override
    public StructableExcelVo parse(FileDto fileDto) {
        return null;
    }

    public StructableExcelVo xlsx2String(String path) throws IOException {
        StructableExcelVo excelVo = new StructableExcelVo();
        StringBuilder content = new StringBuilder();
        XSSFWorkbook workbook = new XSSFWorkbook(path);
        List<String> sheets = new ArrayList<>();
        for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {
            if (null != workbook.getSheetAt(numSheets)) {
                XSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet
                sheets.add(aSheet.getSheetName());
                for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet
                        .getLastRowNum(); rowNumOfSheet++) {
                    if (null != aSheet.getRow(rowNumOfSheet)) {
                        XSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 获得一个行
                        for (short cellNumOfRow = 0; cellNumOfRow <= aRow
                                .getLastCellNum(); cellNumOfRow++) {
                            if (null != aRow.getCell(cellNumOfRow)) {
                                XSSFCell aCell = aRow.getCell(cellNumOfRow);// 获得列值
                                if (convertCell(aCell).length() > 0) {
                                    content.append(convertCell(aCell));
                                }
                            }
                            content.append("\n");
                        }
                    }
                }
            }
        }
        excelVo.setContent(content.toString());
        excelVo.setSheets(sheets);
        return excelVo;
    }

    public StructableExcelVo xls2String(String path) throws IOException {
        StructableExcelVo excelVo = new StructableExcelVo();
        StringBuilder content = new StringBuilder();
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));// 创建对Excel工作簿文件的引用
        List<String> sheets = new ArrayList<>();
        for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {
            if (null != workbook.getSheetAt(numSheets)) {
                HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet
                sheets.add(aSheet.getSheetName());
                for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet
                        .getLastRowNum(); rowNumOfSheet++) {
                    if (null != aSheet.getRow(rowNumOfSheet)) {
                        HSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 获得一个行
                        for (short cellNumOfRow = 0; cellNumOfRow <= aRow
                                .getLastCellNum(); cellNumOfRow++) {
                            if (null != aRow.getCell(cellNumOfRow)) {
                                HSSFCell aCell = aRow.getCell(cellNumOfRow);// 获得列值
                                if (convertCell(aCell).length() > 0) {
                                    content.append(convertCell(aCell));
                                }
                            }
                            content.append("\n");
                        }
                    }
                }
            }
        }
        excelVo.setContent(content.toString());
        excelVo.setSheets(sheets);
        return excelVo;
    }

    private String convertCell(Cell cell) {
        NumberFormat formater = NumberFormat.getInstance();
        formater.setGroupingUsed(false);
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                cellValue = formater.format(cell.getNumericCellValue());
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case BLANK:
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                cellValue = Boolean.valueOf(cell.getBooleanCellValue()).toString();
                break;
            case ERROR:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
            default:
                cellValue = "";
        }
        return cellValue.trim();
    }

    public static void main(String[] args) throws IOException {
        ExcelParser wd = new ExcelParser();
        StructableExcelVo excelVo = wd.parse("E:\\002_Model\\003\\工作目录\\采购订单326.xlsx");
        System.out.println(excelVo.getContent());
        for (int i = 0; i < excelVo.getSheets().size(); i++) {
            System.out.println(excelVo.getSheets().get(i));
        }
    }
}
